from DBUtils.PooledDB import PooledDB, SharedDBConnection
import psutil
import pymysql
import hashlib
from flask import session, request,current_app
POOL = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
            maxcached=5,  # 链接池中最多闲置的链接，0和None不限制
            maxshared=3,
            # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created,
            # 4 = when a query is executed, 7 = always
            host='mysql.fzj.com',
            port=3306,
            user='fzj',
            password='FanYan879688',
            database='csdn',
            charset='utf8'
        )
conn = POOL.connection()
def connect():
    '''
    启动连接
    :return:
    '''
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    conn.ping(reconnect=True)
    return conn, cursor

def connect_close(conn, cursor):
    '''
    关闭连接
    :param conn:
    :param cursor:
    :return:
    '''
    conn.commit()
    cursor.close()

def execute(conn,cursor,sql,parm=None):
    '''
    执行sql语句
    :param conn: 连接对象
    :param cursor: 游标
    :param sql: sql语句
    :param parm: 参数
    :return:
    '''
    res=cursor.execute(sql, parm)
    conn.commit()
    return res

# cpu、内存监控
def getMemCpu():
    data = psutil.virtual_memory()
    total = round(data.total/1024/1024/1024,2) #总内存,单位为gb
    free = round(data.available/1024/1024/1024,2) #空余内存
    memory = int(round(data.percent))
    cpu = psutil.cpu_percent(interval=1)
    return {'memory':memory,'cpu':cpu,'total':total,'free':free}

# 自定义缓存名
def make_cache_key():
    """Dynamic creation the request url.自定义缓存名"""
    if(len(request.path)>1):
        path = request.path+'/'
    else:
        path=request.path
    h1 = hashlib.md5()
    item_dict=[item for item in request.args.items()]
    if request.form:
        item_dict.extend([item for item in request.form.items()])
    if 'admin' in path:
        item_dict.extend([str(item) for item in session.values()])
    h1.update(str(frozenset(item_dict)).encode())
    return 'csdn_' + path + h1.hexdigest()